﻿CREATE PROCEDURE [dbo].[proc_Statistics_Financial_Today_ZhiChu_G]
	(
		@CompanyId VARCHAR(500),
		@Today nchar(10)
	)
AS
BEGIN
	DECLARE @strSql VARCHAR(4000) , @strSql1 VARCHAR(4000) , @strSql2 VARCHAR(4000) , @strSql3 VARCHAR(4000) 
	set @strSql2=''
	
	if @CompanyId<>'0'
	begin
		set @strSql2=' where Id in ('+@CompanyId+') '
	end
	
	
	SET @strSql='
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, '''+@Today + ' 00:00:00'')
	Set @eRq = Convert(Datetime, '''+@Today + ' 23:59:59'')
	
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY c.Id ASC)AS Row
	,c.Id As CompanyId
	,c.CompanyName
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=20 and CreateDate>=@sRq and CreateDate<=@eRq),0)
	-isnull((Select sum(je) From [AB_List] Where CompanyId =c.Id And rootId=2 And AB_Type=1 And UpTime>=@sRq and UpTime<=@eRq),0)  As OAA--直接成本
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=21 and CreateDate>=@sRq and CreateDate<=@eRq),0) As OAB--间接成本
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=8 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=8 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OBA --市场费用
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=1 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=1 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OBB--日常生活
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=5 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=5 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OBC--行政办公
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=9 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=9 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OBD--财务费用
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=10 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=10 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OBE--人力费用
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=11 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=11 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OBF--部门基金
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=7 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=7 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OBG--总办费用
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=12 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=12 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OBH--其他费用
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=25 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=25 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OCA--投资
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=18 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=18 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OCB--支出借款
	'
	SET @strSql1='
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyId=c.Id and CateTwoId=26 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=26 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OCC--往来借款
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=23 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=23 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As ODA--有形资产
	
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=24 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=24 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As ODB--无形资产
		,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=27 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
		ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=27 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEA--间接成本
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=28 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=28 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEB--市场费用
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=29 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=29 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEC--日常生活
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=30 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=30 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OED--行政办公
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=31 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=31 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEE--财务费用
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=32 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=32 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEF--人力费用
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=33 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=33 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEG--部门基金
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=34 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=34 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEH--总办费用
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=35 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=35 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEI--其他费用
	'
	SET @strSql2='
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=36 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=36 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEJ--投资	
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=37 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=37 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEK--支出借款
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=38 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=38 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEL--往来借款
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=39 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=39 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEM--有形资产
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=40 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=40 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEN--无形资产
	
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=41 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=41 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEO--有形资产
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=42 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=42 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEP--无形资产
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=12 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=12 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OEQ--无形资产
     ,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=44 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
     ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=44 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OER--上缴基金
	,ISNULL((Select Sum(OutlayPrice) From Finance_Outlay Where CompanyIdcur=c.Id and CateTwoId=45 and CreateDate>=@sRq and CreateDate<=@eRq),0)+
	ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=c.Id and TypeId=1  and CateTwoId=45 and CreateDate>=@sRq and CreateDate<=@eRq ),0) As OES--其他成本支出                                                                                                                                                        
	                                                                                                                                                             
	From Company c  '+@strSql2+'
	)
	'
	SET @strSql3='
	Select * From list,(SELECT SUM(OAA) AS OAA_a,SUM(OAB) AS OAB_a,SUM(OBA) AS OBA_a,SUM(OBB) AS OBB_a
	,SUM(OBC) AS OBC_a,SUM(OBD) AS OBD_a,SUM(OBE) AS OBE_a,SUM(OBF) AS OBF_a
	,SUM(OBG) AS OBG_a,SUM(OBH) AS OBH_a,SUM(OCA) AS OCA_a,SUM(OCB) AS OCB_a,SUM(OCC) AS OCC_a
	,SUM(ODA) AS ODA_a,SUM(ODB) AS ODB_a
	,SUM(OEA) AS OEA_a,SUM(OEB) AS OEB_a,SUM(OEC) AS OEC_a,SUM(OED) AS OED_a,SUM(OEE) AS OEE_a,SUM(OEF) AS OEF_a
	,SUM(OEG) AS OEG_a,SUM(OEH) AS OEH_a,SUM(OEI) AS OEI_a,SUM(OEJ) AS OEJ_a,SUM(OEK) AS OEK_a,SUM(OEL) AS OEL_a
	,SUM(OEM) AS OEM_a,SUM(OEN) AS OEN_a,SUM(OEO) AS OEO_a,SUM(OEP) AS OEP_a,SUM(OEQ) AS OEQ_a
	,SUM(OER) AS OER_a,SUM(OES) AS OES_a
	 FROM list) AS n'
	 
	 print (@strSql2)
	  EXEC(@strSql+@strSql1+@strSql2+@strSql3)
End
	RETURN
